from sqlalchemy.orm import Session
from sqlalchemy import and_, or_, func
from typing import List, Optional
from datetime import datetime
import uuid
from app.models.booking import Booking, BookingStatus
from app.models.flight import Flight
from app.models.passenger import Passenger
from app.schemas.booking import BookingCreate, BookingUpdate, BookingWithDetails

import logging
logger = logging.getLogger(__name__)

class BookingService:
    def __init__(self, db: Session):
        logger.warning(db)
        self.db = db

    def generate_booking_number(self) -> str:
        """生成唯一的预订号"""
        return f"BK{datetime.now().strftime('%Y%m%d')}{uuid.uuid4().hex[:6].upper()}"

    def get_available_seats(self, flight_id: int) -> List[str]:
        """获取航班的可用座位"""
        # 获取航班信息
        flight = self.db.query(Flight).filter(Flight.id == flight_id).first()
        if not flight:
            return []

        # 获取已预订的座位
        booked_seats = self.db.query(Booking.seat_number).filter(
            and_(
                Booking.flight_id == flight_id,
                Booking.booking_status != BookingStatus.cancelled
            )
        ).all()

        booked_seats_set = {seat[0] for seat in booked_seats if seat[0]}

        # 生成所有可能的座位号（经济舱示例：1-30排，每排6座）
        all_seats = []
        for row in range(1, 31):
            for seat in ['A', 'B', 'C', 'D', 'E', 'F']:
                all_seats.append(f"{row}{seat}")

        # 返回可用座位
        return [seat for seat in all_seats if seat not in booked_seats_set]

    def create_booking(self, booking: BookingCreate) -> Optional[Booking]:
        """创建新的预订"""
        # 验证乘客和航班存在
        passenger = self.db.query(Passenger).filter(Passenger.id == booking.passenger_id).first()
        if not passenger:
            return None

        flight = self.db.query(Flight).filter(Flight.id == booking.flight_id).first()
        if not flight:
            return None

        # 检查航班是否有可用座位
        if flight.available_seats <= 0:
            return None

        # 检查座位是否已被预订
        if booking.seat_number:
            existing_booking = self.db.query(Booking).filter(
                and_(
                    Booking.flight_id == booking.flight_id,
                    Booking.seat_number == booking.seat_number,
                    Booking.booking_status != BookingStatus.cancelled
                )
            ).first()
            if existing_booking:
                return None

        # 生成预订号
        booking_number = self.generate_booking_number()

        # 创建预订
        db_booking = Booking(
            booking_number=booking_number,
            passenger_id=booking.passenger_id,
            flight_id=booking.flight_id,
            seat_number=booking.seat_number,
            price=booking.price,
            booking_status=BookingStatus.confirmed
        )

        # 更新航班可用座位
        flight.available_seats -= 1

        self.db.add(db_booking)
        self.db.commit()
        self.db.refresh(db_booking)
        return db_booking

    def get_booking_by_number(self, booking_number: str) -> Optional[BookingWithDetails]:
        """根据预订号获取预订（包含航班和乘客详细信息）"""
        booking = self.db.query(Booking).join(Flight).join(Passenger).filter(
            Booking.booking_number == booking_number
        ).first()
        
        if not booking:
            return None
            
        return BookingWithDetails(
            id=booking.id,
            booking_number=booking.booking_number,
            passenger={
                "id": booking.passenger.id,
                "first_name": booking.passenger.first_name,
                "last_name": booking.passenger.last_name,
                "email": booking.passenger.email,
                "phone": booking.passenger.phone
            },
            flight={
                "id": booking.flight.id,
                "flight_number": booking.flight.flight_number,
                "departure_city": booking.flight.departure_city,
                "arrival_city": booking.flight.arrival_city,
                "departure_time": booking.flight.departure_time,
                "arrival_time": booking.flight.arrival_time,
                "departure_airport": booking.flight.departure_airport,
                "arrival_airport": booking.flight.arrival_airport
            },
            seat_number=booking.seat_number,
            booking_status=booking.booking_status,
            booking_date=booking.booking_date,
            price=booking.price
        )

    def get_bookings_by_passenger(self, passenger_id: int) -> List[Booking]:
        """获取乘客的所有预订（包含航班详细信息）"""
        return self.db.query(Booking).join(Flight).filter(
            Booking.passenger_id == passenger_id
        ).order_by(Booking.booking_date.desc()).all()

    def get_bookings_by_flight(self, flight_id: int) -> List[Booking]:
        """获取航班的所有预订（包含乘客详细信息）"""
        return self.db.query(Booking).join(Passenger).filter(
            Booking.flight_id == flight_id
        ).order_by(Booking.booking_date.desc()).all()

    def get_bookings(self, skip: int = 0, limit: int = 100) -> List[Booking]:
        """获取所有预订（包含航班和乘客详细信息）"""
        return self.db.query(Booking).join(Flight).join(Passenger).offset(skip).limit(limit).all()

    def cancel_booking(self, booking_number: str) -> Optional[Booking]:
        """取消预订"""
        booking = self.get_booking_by_number(booking_number)
        if not booking or booking.booking_status == BookingStatus.cancelled:
            return None
            
        # 更新预订状态
        booking.booking_status = BookingStatus.cancelled
        
        # 恢复航班可用座位
        flight = self.db.query(Flight).filter(Flight.id == booking.flight_id).first()
        if flight:
            flight.available_seats += 1
            
        self.db.commit()
        self.db.refresh(booking)
        return booking

    def update_seat(self, booking_number: str, new_seat_number: str) -> Optional[Booking]:
        """更新座位选择"""
        booking = self.get_booking_by_number(booking_number)
        logger.warning(booking.seat_number)
        logger.warning(booking.booking_status)
        logger.warning(booking.flight_id)
        if not booking or booking.booking_status == BookingStatus.cancelled:
            return None
            
        # 检查新座位是否可用
        existing_booking = self.db.query(Booking).filter(
            and_(
                Booking.flight_id == booking.flight_id,
                Booking.seat_number == new_seat_number,
                Booking.booking_status != BookingStatus.cancelled
            )
        ).first()
        
        if existing_booking:
            return None
            
        # 更新座位
        booking.seat_number = new_seat_number
        self.db.commit()
        self.db.refresh(booking)
        return booking

    def search_bookings(self, booking_number: Optional[str] = None, passenger_name: Optional[str] = None, flight_number: Optional[str] = None) -> List[Booking]:
        """搜索预订（根据预订号、乘客姓名、航班号）"""
        query = self.db.query(Booking).join(Passenger).join(Flight)
        
        conditions = []
        if booking_number:
            conditions.append(Booking.booking_number.ilike(f"%{booking_number}%"))
        if passenger_name:
            conditions.append(or_(
                Passenger.first_name.ilike(f"%{passenger_name}%"),
                Passenger.last_name.ilike(f"%{passenger_name}%")
            ))
        if flight_number:
            conditions.append(Flight.flight_number.ilike(f"%{flight_number}%"))
        
        if conditions:
            query = query.filter(or_(*conditions))
        
        return query.all()

    def get_booking_stats(self) -> dict:
        """获取预订统计信息"""
        total_bookings = self.db.query(Booking).count()
        confirmed_bookings = self.db.query(Booking).filter(
            Booking.booking_status == BookingStatus.confirmed
        ).count()
        cancelled_bookings = self.db.query(Booking).filter(
            Booking.booking_status == BookingStatus.cancelled
        ).count()
        pending_bookings = self.db.query(Booking).filter(
            Booking.booking_status == BookingStatus.pending
        ).count()
        
        total_revenue = self.db.query(Booking).filter(
            Booking.booking_status == BookingStatus.cancelled
        ).with_entities(func.sum(Booking.price)).scalar() or 0
        
        return {
            "total_bookings": total_bookings,
            "confirmed_bookings": confirmed_bookings,
            "cancelled_bookings": cancelled_bookings,
            "pending_bookings": pending_bookings,
            "total_revenue": float(total_revenue),
            "average_booking_price": float(total_revenue / confirmed_bookings) if confirmed_bookings > 0 else 0
        }